set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
alter  PROCEDURE [dbo].[a_Articles_selListByCondition]
@MainCatID varchar(50), 
@SubCatID varchar(50), 
@fkCountry varchar(50), 
@fYear varchar(50), 
@fkSeason varchar(50), 
@Permission varchar(50),
@SortBy varchar(50),
@SortOrder varchar(50),
@keyword	nvarchar(500),
@DateFrom varchar(50),
@DateTo varchar(50),
@PublishStatus bit,
@Page int,
@PageSize int,
@TotalRecords int output
AS
Set NoCount On
DECLARE @strSQL nvarchar(3000), @strCriteria nvarchar(1000), @strSort nvarchar(200)
--DECLARE @FirstRec int, @LastRec int, @TotalRecords int, @TotalPages int, @TotalPageSet int, @CurrentSet int, @SetLBound int, @SetUBound int, @PageSize int, @PageSetSize int
DECLARE @MainImgNo int, @strTop varchar(6)

SET @strCriteria=''
SET @strSort=''
SET @strTop=''

CREATE TABLE #Temp(
	ID int IDENTITY, 
	AID int	
)
--- search keyword
if len(@keyword)=0 
	begin 
		SET @strCriteria=@strCriteria+ ' AND PublishStatus='+ cast( @PublishStatus as varchar(50))
if len(@DateFrom)>0
		SET @strCriteria=@strCriteria+ ' AND PostDate>=CONVERT(DATETIME, '+ @DateFrom+', 102)'
if len(@DateTo)>0
		SET @strCriteria=@strCriteria+ ' AND PostDate<=CONVERT(DATETIME, '+ @DateTo+', 102)'

		IF LEN(@MainCatID)> 0	
			SET @strCriteria=@strCriteria+' AND MainCatID= ' + @MainCatID
		IF CAST(@SubCatID as int) > 0
		    BEGIN
			SET @strCriteria=@strCriteria+' AND SubCatID= ' + @SubCatID
			--SELECT @SubCatName=Name, @uSubCatName=uName FROM a_CatSub WHERE SubCatID=@SubCatID
		    END
		
		IF LEN(@fkCountry) > 0
			SET @strCriteria=@strCriteria+' AND CountryID = ' + @fkCountry
		IF LEN(@fYear) > 0
			SET @strCriteria=@strCriteria+' AND FYear= ' + @fYear
		IF CAST(@fkSeason as int) > 0				
			SET @strCriteria=@strCriteria+' AND SeasonID= ' + @fkSeason			
		IF LEN(@Permission) > 0
			SET @strCriteria=@strCriteria+' AND Permission= ' + @Permission
		IF LEN(@SortBy) > 0
			SET @strSort = ' ORDER BY ' + @SortBy + ' '+@SortOrder
		ELSE
			SET @strSort = 'ORDER BY PublishDate DESC'
		--Top 20 for listing home
		IF @SubCatID = '0'
		    BEGIN
			SET @strTop = 'TOP 20'
			SET @strSort = ' ORDER BY statPopularity DESC'
			--SELECT @SubCatName=Name, @uSubCatName=uName FROM a_CatMain WHERE CatID=@MainCatID
		    END
			
		SET @strSQL= 'INSERT INTO #Temp (AID) 
		SELECT '+@strTop+' ID FROM a_Articles WHERE (1=1) '+@strCriteria+ @strSort
		EXEC(@strSQL)
	end
else
	begin
		if isnumeric(@keyword)=1
			begin
				declare @aid int
				select @aid=aid
				from a_articles_img where imageno=cast(@keyword as int)
				INSERT INTO #Temp (AID)
				SELECT ID
				FROM a_Articles 
				WHERE (1=1) --(PublishStatus=1) 
				and id=@aid
			end
		else
			begin
				set @keyword=replace(@keyword,'''','')
				set @keyword=replace(@keyword,'"','')
				set @keyword='"'+@keyword+'"'		

				INSERT INTO #Temp (AID)
				SELECT ID
				FROM a_Articles 
				WHERE (1=1)--(PublishStatus=1) 
				and (
					 uTitle LIKE N'%'+@keyword +'%' 
					or uBody LIKE N'%'+@keyword+'%' 
					or UTitle LIKE N'%'+@keyword+'%'
					or UBody LIKE N'%'+ @keyword+'%'
				)
				if not exists(select count(*) from #Temp)
				INSERT INTO #Temp (AID)
				SELECT ID
				FROM a_Articles 
				WHERE (1=1)--(PublishStatus=1) 
				and (
					 uTitle LIKE N'%'+@keyword +'%' 
					or uBody LIKE N'%'+@keyword+'%' 
					or UTitle LIKE N'%'+@keyword+'%'
					or UBody LIKE N'%'+ @keyword+'%'
				)	
			end
	end

SELECT @TotalRecords = COUNT(*) FROM #Temp
--print cast(@TotalRecords as varchar(10)) 

SELECT
	[dbo].[a_Articles].[ID],
	[UTitle],
	[KTitle],
	[UAuthor],
	[UBody],
	[KBody],
	[MainCatID],
	[SubCatID],
	[CountryID],
	[SeasonID],
	[FYear],
	[PostDate],
	[PostBy_UserID],
	[PublishDate],
	[PublishStatus],
	[StatPopularity],
	[StatRating],
	[Permission],
	[IsFeatured],
	[FeaturedType],
	[LayoutFormat],
	[UniqueKeywords],
	[IsEdited],
	[Brand],
	[GroupID]
FROM
	#Temp AS tblTemp JOIN [dbo].[a_Articles] ON
	tblTemp.AID = [dbo].[a_Articles].ID 	
WHERE (@PageSize = 0) OR (@PageSize > 0 AND (@Page - 1)*@PageSize < tblTemp.ID AND tblTemp.ID <= @Page*@PageSize)
ORDER BY tblTemp.ID


----Update Stats: Read
--DECLARE @DocType int, @EventType int, @EventDate char(12), @DocID int, @ThisMonthViews int, @StatDate varchar(30)
--	--Update stats
--	SET @DocType=1 --listing/category
--	SET @EventType=1 --viewing
--	
--	IF @SubCatID = '0' --Handle top 20, where subcat=0, add 200 to main cat to identify
--		SET @SubCatID='200'+@MainCatID
--	
--	SET @DocID=CAST(@SubCatID AS int)
--	
--	--Update Stat Detail
--	SET @EventDate=CONVERT(char(12), GETDATE(), 101)
--	IF NOT EXISTS (SELECT 'true' FROM a_StatsByDate WHERE (EventDate = @EventDate) AND (DocType = @DocType) AND (EventType = @EventType) AND (DocID = @DocID))
--		INSERT INTO a_StatsByDate (EventDate, DocType, EventType, DocID, Counts) VALUES (@EventDate,@DocType,@EventType,@DocID,1)
--	ELSE
--		UPDATE a_StatsByDate SET Counts=Counts+1 WHERE (EventDate = @EventDate) AND (DocType = @DocType) AND (EventType = @EventType) AND (DocID = @DocID)
--	
--	--Update Stat Summary
--	UPDATE a_CatSub SET statPopularity=statPopularity+1 WHERE SubCatID = @DocID
--
--	--Update Stat Summary
--		SELECT  @ThisMonthViews=SUM(Counts) FROM a_StatsByDate WHERE (DocType=@DocType) AND (EventType=@EventType) AND (DocID=@DocID) AND (YEAR(EventDate) = YEAR(GETDATE())) AND (MONTH(EventDate) = MONTH(GETDATE()))
--		SET @StatDate=CONVERT(Varchar(2),MONTH(GETDATE()))+'/1/'+CONVERT(varchar(4),YEAR(GETDATE()))+' 12:00:00 AM'
--	
--		IF NOT EXISTS(SELECT 'True' FROM a_StatsByMonth WHERE (DocType=@DocType) AND (DocID=@DocID) AND (StatDate=@StatDate))
--			INSERT INTO a_StatsByMonth (StatDate, DocType, DocID, Viewed) VALUES (@StatDate,@DocType,@DocID,@ThisMonthViews)
--		ELSE
--			UPDATE a_StatsByMonth SET Viewed=@ThisMonthViews WHERE (DocType=@DocType) AND (DocID=@DocID) AND (StatDate =@StatDate)




